﻿Imports MySql.Data.MySqlClient

Public Class Form4
    Dim con As MySqlConnection
    Dim cmd As MySqlCommand
    Dim da As MySqlDataAdapter
    Dim dr As MySqlDataReader
    Dim result As Integer
    Dim query As String
    Dim dt As DataTable
    Dim bs As BindingSource

    Private Sub TextBox5_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtContact.TextChanged
        Dim con As New MySqlConnection("server=localhost;userid=root;password=;database=librarysys")

        Try
            con.Open()
            cmd = New MySqlCommand("select * from librarysys.tblborrower where borrowerID='" & txtBorrowId.Text & "' and name='" & txtName.Text & "' and address='" & txtAddress.Text & "' and contactNo='" & txtContact.Text & "' and emailAdd='" & txtEmail.Text & "' and note='" & txtNote.Text & "' and transDate='" & dtpTrans.Text & "' ", con)
            result = cmd.ExecuteScalar
            con.Close()
            If result = 0 Then
                con.Open()
                cmd = New MySqlCommand("INSERT INTO librarysys.tblborrower (borrowerID,name,address,contactNo,emailAdd,note,transDate)values('" & txtBorrowId.Text & "','" & txtName.Text & "','" & txtAddress.Text & "','" & txtContact.Text & "','" & txtEmail.Text & "','" & txtNote.Text & "','" & dtpTrans.Text & "')", con)
                cmd.ExecuteNonQuery()
                MessageBox.Show("data saved")
                con.Close()
            Else
                MessageBox.Show("data exist")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try

    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        txtBorrowId.Text = Nothing
        txtName.Text = Nothing
        txtAddress.Text = Nothing
        txtContact.Text = Nothing
        txtEmail.Text = Nothing
        txtNote.Text = Nothing
    End Sub

    Private Sub btnRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRefresh.Click

    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim dv As New DataView(dt)
        dv.RowFilter = String.Format("name like '%{0}%' ", txtName.Text)
        DataGridView1.DataSource = dv
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        
    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim con As New MySqlConnection("server=localhost;userid=root;password=;database=librarysys")
        con.Close()
        Try
            con.Open()
            cmd = New MySqlCommand("select * from librarysys.tblborrower where borrowerID='" & txtBorrowId.Text & "' and name='" & txtName.Text & "' and address='" & txtAddress.Text & "' and contactNo='" & txtContact.Text & "' and emailAdd='" & txtEmail.Text & "' and note='" & txtNote.Text & "' and transaction_date='" & dtpTrans.Text & "' ", con)
            result = cmd.ExecuteScalar
            con.Close()
            If result >= 1 Then
                con.Open()
                cmd = New MySqlCommand("delete from tblborrower where borrowerID='" & txtBorrowId.Text & "' and name='" & txtName.Text & "' ", con)
                cmd.ExecuteNonQuery()
                MessageBox.Show("data deleted")
                con.Close()
            Else
                MessageBox.Show("data not found")
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            con.Dispose()
        End Try
    End Sub
End Class